package com.staples.tc.dao;

import static com.staples.tc.util.TCIConstants.DB_COL_CCARD;
import static com.staples.tc.util.TCIConstants.DB_COL_DEFCARD;
import static com.staples.tc.util.TCIConstants.DB_DRIVER_CLASS;
import static com.staples.tc.util.TCIConstants.KEY_DB_CONNURL;
import static com.staples.tc.util.TCIConstants.KEY_DB_PASSWORD;
import static com.staples.tc.util.TCIConstants.KEY_DB_USERID;
import static com.staples.tc.util.TCIConstants.QUERY_CCARD_LOOKUP;
import static com.staples.tc.util.TCIConstants.QUERY_COMP_LOOKUP;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.log4j.Logger;

import com.staples.security.crypto.utilities.CryptoException;
import com.staples.security.crypto.utilities.CryptoManager;
import com.staples.tc.exception.CCardJobException;
import com.staples.tc.util.TCIResourceBundle;

public class CCardDAO {
	private static final Logger logger = Logger.getLogger(CCardDAO.class);

	private static Connection connection = null;
	private static PreparedStatement cCardPs;
	private static PreparedStatement defCCardPs;
	
	/**
	 * make JDBC connection 
	 * @return
	 * @throws CCardJobException
	 */
	public boolean createDbConnection() throws CCardJobException {
		logger.debug("Entering createDbConnection() ....");
		boolean flag = false;
		try {
			if (connection == null) {
				Class.forName(DB_DRIVER_CLASS);
				CryptoManager cryptoManager = new CryptoManager("STAPLES");

				String dbConnUrl = TCIResourceBundle.getProperty(KEY_DB_CONNURL);
				String dbUserId = TCIResourceBundle.getProperty(KEY_DB_USERID);
				String dbPasswd = TCIResourceBundle.getProperty(KEY_DB_PASSWORD);
				logger.debug("Oracle JDBC driver found ....");
//				logger.debug("Connection url :" + dbConnUrl);
				connection = DriverManager.getConnection(dbConnUrl, dbUserId, cryptoManager.decrypt(dbPasswd));
			}
			
			if (connection != null && !connection.isClosed()) {
				flag = true;
			}
		} catch (SQLException sExp) {
			logger.error("SQLException occured ..." + sExp.getMessage());
			throw new CCardJobException(sExp.getMessage(), sExp);
		} catch (CryptoException cExp) {
			logger.error("CryptoException occured ..." + cExp.getMessage());
			throw new CCardJobException(cExp.getMessage(), cExp);
		} catch (Exception exp) {
			logger.error("Exception occured ..." + exp.getMessage());
			throw new CCardJobException(exp.getMessage(), exp);
		} 
		logger.debug("Exiting createDbConnection() ....");
		return flag;
	}

	
	/**
	 * get C-Card number 
	 * @param rewardNumber
	 * @return
	 * @throws CCardJobException
	 */
	public String getCCard(String rewardNumber) throws CCardJobException {
		logger.debug("Entering getCCard() ....");
		String cCardNum = null;
		ResultSet resultSet = null;
		try {
			if (cCardPs == null) {
				cCardPs = connection.prepareStatement(QUERY_CCARD_LOOKUP);
			}
			cCardPs.setString(1, rewardNumber);
			resultSet = cCardPs.executeQuery();
			logger.debug("Query executed at DAO class");

			if (resultSet.next()) {
				cCardNum = resultSet.getString(DB_COL_CCARD);
			}
		} catch (SQLException sExp) {
			logger.error("SQLException occured ..." + sExp.getMessage());
			throw new CCardJobException(sExp.getMessage(), sExp);
		} catch (Exception exp) {
			logger.error("Exception occured ..." + exp.getMessage());
			throw new CCardJobException(exp.getMessage(), exp);
		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
				}
			} catch (Exception exp) {
				logger.error("Exception while closing result set ...", exp);
			}
		}
		logger.debug("Exiting getCCard() ....");
		return cCardNum;
	}

	/**
	 * get default Card number
	 * @param companyName
	 * @return
	 * @throws CCardJobException
	 */
	public String getDefaultCard(String companyName) throws CCardJobException {
		logger.debug("Entering getDefaultCard() ....");
		String defCCardNum = null;
		ResultSet resultSet = null;
		try {
			if (defCCardPs == null) {
				defCCardPs = connection.prepareStatement(QUERY_COMP_LOOKUP);
			}
			defCCardPs.setString(1, companyName);
			resultSet = defCCardPs.executeQuery();
			logger.debug("Query executed at DAO class ...");

			if (resultSet.next()) {
				defCCardNum = resultSet.getString(DB_COL_DEFCARD);
			}
		} catch (SQLException sExp) {
			logger.error("SQLException occured ..." + sExp.getMessage());
			throw new CCardJobException(sExp.getMessage(), sExp);
		} catch (Exception exp) {
			logger.error("Exception occured ..." + exp.getMessage());
			throw new CCardJobException(exp.getMessage(), exp);
		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
				}
			} catch (Exception exp) {
				logger.error("Exception while closing result set ...", exp);
			}
		}

		logger.debug("Exiting getDefaultCard() ....");
		return defCCardNum;
	}
	
	public void closeAllDbResources(){
		try {
			if (cCardPs != null) {
				cCardPs.close();
				cCardPs = null;
			}
			if (defCCardPs != null) {
				defCCardPs.close();
				defCCardPs = null;
			}
			if (connection != null && !connection.isClosed()) {
				connection.close();
				connection = null;
			}
		} catch (Exception exp) {
			logger.error("Exception while closing Db resources ...", exp);
		}
	}

}
